package com.supermap.application.service;

import com.alibaba.fastjson.JSONObject;
import com.supermap.application.common.IndexInfoVO;
import com.supermap.application.common.IndexVO;
import com.supermap.application.dao.*;
import com.supermap.application.entity.*;
import com.supermap.application.utils.ExcelUtils;
import com.supermap.application.utils.TableBuilderUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.io.File;
import java.io.FileOutputStream;
import java.net.URLEncoder;
import java.util.*;

@Service
@Transactional
public class TargetManageService {

    @Autowired
    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    private TargetManageIndexSysDao targetManageIndexSysDao;

    @Autowired
    private TargetManageIndexSysTypeDao targetManageIndexSysTypeDao;

    @Autowired
    private TargetManageIndexSysTypeColDao targetManageIndexSysTypeColDao;

    @Autowired
    private TargetManageIndexSysDataDao targetManageIndexSysDataDao;

    @Autowired
    private DepartmentDao departmentDao;

    @Value("${target-download-excel}")
    private String targetDownloadExcel;

    public String test(){
        return "targetManager";
    }

    /**
     * 查询所有目标体系
     * @return
     */
    public List findAllIndexSys(String year){
        List<TIndexSys> allIndexSys = this.targetManageIndexSysDao.findAlL(year);
        return allIndexSys;
    }

    /**
     * 根据目标体系名称查询指标类型
     * @param name
     * @return
     */
    public List findIndexSysTypeByName(String name){
        TIndexSys tIndexSys = this.targetManageIndexSysDao.findCodeByName(name);
        List<TIndexSysType> allBySysCode = this.targetManageIndexSysTypeDao.findAllBySysCode(tIndexSys.getCode());
        return allBySysCode;
    }

    /**
     * 根据目标指标类型主键id查询具体指标
     * @param id
     * @return
     */
    public List findIndexSysTypeColByName(String id){
        //TIndexSysType tIndexSysType = this.targetManageIndexSysTypeDao.findByName(name);
        List<TIndexSysTypeCol> allByType_id = this.targetManageIndexSysTypeColDao.findAllByType_id(id);
        List list = new ArrayList();
        for (TIndexSysTypeCol tIndexSysTypeCol : allByType_id){
            if(tIndexSysTypeCol.getPid() == null){
                JSONObject treeObject = new JSONObject();
                treeObject.put("id",tIndexSysTypeCol.getId());
                treeObject.put("text",tIndexSysTypeCol.getName());
                if (tIndexSysTypeCol.getUnit() != null){
                    treeObject.put("unit",tIndexSysTypeCol.getUnit());
                }
                String type = tIndexSysTypeCol.getType();
                if (type != null || !type.equals("")){
                    if (type.equals("month")){
                        treeObject.put("type",type);
                    }else if (type.equals("season")){
                        treeObject.put("type",type);
                    }else if (type.equals("year")){
                        treeObject.put("type",type);
                    }
                }
                if (tIndexSysTypeCol.getPid() == null){
                    if (getChildren(tIndexSysTypeCol.getId()).size() > 0) {
                        treeObject.put("nodes", getChildren(tIndexSysTypeCol.getId()));
                    }

                }
                list.add(treeObject);
            }

        }
        return list;
    }

    /**
     * 获取子节点
     * @param id
     * @return
     */
    public List getChildren(String id){
        List list = new ArrayList();
        List<TIndexSysTypeCol> allByType_id = null;
        if (id != null || !id.equals("")){
            allByType_id = this.targetManageIndexSysTypeColDao.findAllByPid(id);
        }
        if (allByType_id.size()>0){
            for (TIndexSysTypeCol tIndexSysTypeCol : allByType_id) {
                //if (tIndexSysTypeCol.getPid() == id){
                    JSONObject jsonObject = new JSONObject(true);
                    jsonObject.put("id",tIndexSysTypeCol.getId());
                    jsonObject.put("text",tIndexSysTypeCol.getName());
                    if (tIndexSysTypeCol.getPid() != null){
                        jsonObject.put("pid",tIndexSysTypeCol.getPid());
                    }else {
                        jsonObject.put("pid","");
                    }
                    if (tIndexSysTypeCol.getUnit() != null){
                        jsonObject.put("unit",tIndexSysTypeCol.getUnit());
                    }
                    String type = tIndexSysTypeCol.getType();
                    if (type != null || !type.equals("")){
                        if (type.equals("month")){
                            jsonObject.put("type",type);
                        }else if (type.equals("season")){
                            jsonObject.put("type",type);
                        }else if (type.equals("year")){
                            jsonObject.put("type",type);
                        }
                    }
                    if (tIndexSysTypeCol.getPid() != null){
                        if (getChildren(tIndexSysTypeCol.getId()).size() > 0){
                            jsonObject.put("nodes",getChildren(tIndexSysTypeCol.getId()));
                        }
                    }
                    list.add(jsonObject);
                //}

            }
        }
        return list;
    }

    /**
     * 查询所有责任部门
     * @return
     */
    public Map findAllDep(String year, String code){
        String tablename = "T_ECO_" + code + "_" + year;
        String sql = "select distinct t.diqu_name_ from  "+tablename+"  t where t.diqu_ is not null";
        BaseDaoImpl<TIndexSysTypeColData,String> dao = new BaseDaoImpl<>(TIndexSysTypeColData.class,entityManager);
        List query = dao.query(sql);
        Map map = new HashMap();
        map.put("result",query);
        return map;
    }

    /**
     * 根据指标名称查询指标数据
     * @return
     */
    public Map findIndexDataByIndexName(String id,String time, String diquName, String tablename){
        Map map = new HashMap();
        TIndexSysTypeCol tIndexSysTypeCol = this.targetManageIndexSysTypeColDao.findOne(id);
        String report_type = tIndexSysTypeCol.getType();
        if (report_type.equals("month")){
            map.put("type","month");
        } else if (report_type.equals("season")){
            map.put("type","season");
        }else if (report_type.equals("year")){
            map.put("type","year");
        }

        BaseDaoImpl<TIndexSysTypeColData,String> dao = new BaseDaoImpl<>(TIndexSysTypeColData.class,entityManager);
        String querySql = "select t.index_name_, t.time_, t.sj_zs_, t.sj_wcl_, t.jh_zs_,t.diqu_name_ from "+ tablename +" t where t.index_code_ ='"+tIndexSysTypeCol.getCode()+"'  and t.time_ = '"+time+"'  and t.diqu_name_ =  '"+diquName+"'";
        //List<TIndexSysTypeColData> tIndexSysTypeColDatas = this.targetManageIndexSysDataDao.findAllByIndexcode(tIndexSysTypeCol.getCode(), time, diquName);
        List query = dao.query(querySql);
        map.put("result",query);
        return map;
    }

    /**
     * 查询统计图表数据
     * @param
     * @return
     */
    public Map findIndexAllSeasonDataByIndexName(String id, String diquName, String tablename, String year){
        Map map = new HashMap();
        TIndexSysTypeCol tIndexSysTypeCol = this.targetManageIndexSysTypeColDao.findOne(id);
        String report_type = tIndexSysTypeCol.getType();
        if (report_type.equals("month")){
            map.put("type","month");
        } else if (report_type.equals("season")){
            map.put("type","season");
        }else if (report_type.equals("year")){
            map.put("type","year");
        }
        BaseDaoImpl<TIndexSysTypeColData,String> dao = new BaseDaoImpl<>(TIndexSysTypeColData.class,entityManager);
        String type = tIndexSysTypeCol.getType();
        List query = new ArrayList();
        if (type.equals("month")){
            String queryMonthSql = "select t.index_name_, t.time_, t.jh_wcl_, t.jh_zs_, t.sj_wcl_, t.sj_zs_,t.diqu_name_  from  "+ tablename +"  t where t.index_code_ = '" +tIndexSysTypeCol.getCode()+ "'  and t.diqu_name_ = '"+diquName+"'  order by t.time_ asc";
            query = dao.query(queryMonthSql);
        }else if (type.equals("season")){
            String querySeasonSql = "select t.index_name_, t.time_, t.jh_wcl_, t.jh_zs_, t.sj_wcl_, t.sj_zs_,t.diqu_name_  from  "+ tablename +"  t where t.index_code_ = '" +tIndexSysTypeCol.getCode()+ "'  and t.diqu_name_ = '"+diquName+"'  and t.time_ in('"+year+"03','"+year+"06','"+year+"09','"+year+"12') order by t.time_ asc";
            query = dao.query(querySeasonSql);
        }else if (type.equals("season2")){
            String querySeason2Sql = "select t.index_name_, t.time_, t.jh_wcl_, t.jh_zs_, t.sj_wcl_, t.sj_zs_,t.diqu_name_  from  "+ tablename +"  t where t.index_code_ = '" +tIndexSysTypeCol.getCode()+ "'  and t.diqu_name_ = '"+diquName+"'  and t.time_ in('"+year+"02','"+year+"05','"+year+"08','"+year+"11') order by t.time_ asc";
            query = dao.query(querySeason2Sql);
        }
        //List<TIndexSysTypeColData> tIndexSysTypeColDatas = this.targetManageIndexSysDataDao.findAllByIndexcodeAndDiqu_name(tIndexSysTypeCol.getCode(),diquName);
        map.put("result",query);
        return map;
    }



    /**
     * 根据部门名称查询
     * @param depName
     * @return
     */
    public Map findDataGroupByDep(String depName){
        Map map = new HashMap();
        if (depName == null || depName.equals("")){
            List<TIndexSysTypeColData> DatabyGroupIndexCol = this.targetManageIndexSysDataDao.findByGroup();
            map.put("result",DatabyGroupIndexCol);
        }else {
            List<TIndexSysTypeColData> byDiqu_nameGroupByDep = this.targetManageIndexSysDataDao.findByDiqu_nameGroupByDep(depName);
            map.put("result",byDiqu_nameGroupByDep);
        }

        return map;
    }



    /**
     * 添加目标体系 true-添加成功 false-添加失败
     * @param tIndexSys
     * @return
     */
    public boolean createIndexSys(TIndexSys tIndexSys){
        boolean b = checkNameExist(tIndexSys);
        if (b) {
            this.targetManageIndexSysDao.save(tIndexSys);
            BaseDaoImpl<TIndexSysTypeColData,String> dao = new BaseDaoImpl<>(TIndexSysTypeColData.class,entityManager);
            String tablename = "T_" + "ECO_" + tIndexSys.getCode() + "_" + tIndexSys.getYear();
            String createSql = TableBuilderUtils.createTargetIndexSysTable(tablename);
            int i = dao.executeBySql(createSql);
            return true;
        }else {
            return false;
        }
    }

    /**
     * 检查指标体系是否存在 存在返回false,不存在返回true
     * @param tIndexSys
     * @return
     */
    public boolean checkNameExist(TIndexSys tIndexSys){
        TIndexSys oneByYearAndName = this.targetManageIndexSysDao.findOneByYearAndName(tIndexSys.getYear(), tIndexSys.getName(), tIndexSys.getCode());
        if (oneByYearAndName != null){
            return false;
        }else {
            return true;
        }
    }

    /**
     * 唯一查询目标体系
     * @param id
     * @return
     */
    public TIndexSys findById(String id){
        TIndexSys tIndexSys = this.targetManageIndexSysDao.findOne(id);
        return tIndexSys;
    }

    /**
     * 更新指标体系 true-更新成功 false-更新失败
     * @param indexSys
     * @return
     */
    public TIndexSys updateIndexSy(TIndexSys indexSys){
        TIndexSys one = this.targetManageIndexSysDao.findOne(indexSys.getId());
        TIndexSys save = null;
        if (one != null){
            save = this.targetManageIndexSysDao.save(indexSys);
        }
        return save;
    }

    /**
     * 删除指标体系 true-删除成功 false-删除失败
     * @param id
     * @return
     */
    public boolean deleteIndexSys(String id){
        TIndexSys one = this.targetManageIndexSysDao.findOne(id);
        if (one == null){
            return false;
        }
        BaseDaoImpl<TIndexSys,String> dao = new BaseDaoImpl<>(TIndexSys.class,entityManager);
        String year = one.getYear();
        String code = one.getCode();
        String tablename = "T_ECO_" + code + "_" + year;
        String dropSql = "drop table " + tablename;
        int i = dao.executeBySql(dropSql);
        if (i != -1){
            System.out.println("成功删除表" + tablename);
        }
        //删除目标体系
        this.targetManageIndexSysDao.delete(one);
        //删除关联指标类型
        List<TIndexSysType> allBySysCode = this.targetManageIndexSysTypeDao.findAllBySysCode(one.getCode());
        if (allBySysCode.size() > 0){
            for (TIndexSysType tIndexSysType : allBySysCode){
                this.targetManageIndexSysTypeDao.delete(tIndexSysType.getId());
            }
        }
        //删除关联的指标
        List<TIndexSysTypeCol> allByIndex_sys_id = this.targetManageIndexSysTypeColDao.findAllByIndex_sys_id(id);
        if (allByIndex_sys_id.size() > 0){
            for (TIndexSysTypeCol tIndexSysTypeCol : allByIndex_sys_id){
                this.targetManageIndexSysTypeColDao.delete(tIndexSysTypeCol.getId());
            }
        }
        return true;
    }





    /**
     * 添加指标分类 添加成功-true 失败-false
     * @param tIndexSysType
     * @return
     */
    public boolean addIndexSysType(TIndexSysType tIndexSysType){
        boolean b = checkSysTypeExist(tIndexSysType);
        if (b){
            this.targetManageIndexSysTypeDao.save(tIndexSysType);
            return true;
        }else {
            return false;
        }
    }

    /**
     * 指标分类是否存在 存在-false 不存在-true
     * @param tIndexSysType
     * @return
     */
    public boolean checkSysTypeExist(TIndexSysType tIndexSysType){
        TIndexSysType one = this.targetManageIndexSysTypeDao.findByNameAndIndex_sys_code(tIndexSysType.getName(), tIndexSysType.getIndex_sys_code());
        if (one != null){
            return false;
        }else {
            return true;
        }
    }

    /**
     * 修改指标分类
     * @param tIndexSysType
     * @return
     */
    public TIndexSysType updateIndexType(TIndexSysType tIndexSysType){
        TIndexSysType one = this.targetManageIndexSysTypeDao.findOne(tIndexSysType.getId());
        TIndexSysType save = null;
        if (one != null){
            save = this.targetManageIndexSysTypeDao.save(tIndexSysType);
        }
        return save;
    }

    /**
     * 根据目标体系code查询所有指标分类
     * @param sysCode
     * @return
     */
    public List<TIndexSysType> findAllIndexType(String sysCode){
        return this.targetManageIndexSysTypeDao.findAllBySysCode(sysCode);
    }


    /**
     * 删除一个指标类型  true-删除成功 false-删除失败
     * @param id
     * @return
     */
    public boolean deleteIndexType(String id){
        TIndexSysType one = targetManageIndexSysTypeDao.findOne(id);
        if (one != null){
            targetManageIndexSysTypeDao.delete(id);
            List<TIndexSysTypeCol> allByType_id = targetManageIndexSysTypeColDao.findAllByType_id(id);
            if (allByType_id.size() > 0){
                for (TIndexSysTypeCol tIndexSysTypeCol : allByType_id){
                    this.targetManageIndexSysTypeColDao.delete(tIndexSysTypeCol.getId());
                }
            }
            return true;
        }else {
            return false;
        }
    }


    /**
     * 添加或更新指标 IndexSys true-添加成功 false-添加失败
     * @param tIndexSysTypeCol
     * @return
     */
    public boolean addOrUpdateIndexCol(TIndexSysTypeCol tIndexSysTypeCol){
        boolean b = checkIndexColExistInType(tIndexSysTypeCol);
        if (b){
            this.targetManageIndexSysTypeColDao.save(tIndexSysTypeCol);
            return true;
        }else {
            return false;
        }
    }


    /**
     * 更新指标
     * @param tIndexSysTypeCol
     * @return
     */
    public boolean updateIndexCol(TIndexSysTypeCol tIndexSysTypeCol){
        TIndexSysTypeCol one = this.targetManageIndexSysTypeColDao.findOne(tIndexSysTypeCol.getId());
        if (one == null){
            return false;
        }else {
            this.targetManageIndexSysTypeColDao.save(tIndexSysTypeCol);
            return true;
        }

    }


    /**
     * 检查对应目标体系-指标分类下，指标是否添加 false-已添加 true-未添加
     * @param tIndexSysTypeCol
     * @return
     */
    public boolean checkIndexColExistInType(TIndexSysTypeCol tIndexSysTypeCol){
        TIndexSysTypeCol one = this.targetManageIndexSysTypeColDao.findByCodeAndIndex_sys_idAndType_id(tIndexSysTypeCol.getCode(), tIndexSysTypeCol.getIndex_sys_id(), tIndexSysTypeCol.getType_id());
        if (one != null){
            return false;
        }else {
            return true;
        }
    }


    /**
     * 删除节点，若有子节点 不能删除  true-删除成功！ false-删除失败
     * @param id
     * @return
     */
    public boolean deleteIndexCol(String id){
        //是否有子节点
        List<TIndexSysTypeCol> allByPid = this.targetManageIndexSysTypeColDao.findAllByPid(id);
        if (allByPid.size() > 0){
            return false;
        }else {
            this.targetManageIndexSysTypeColDao.delete(id);
            return true;
        }
    }


    /**
     * 查询目标体系下所有指标
     * @param id
     * @return
     */
    public List findAllSysIndexCol(String id){
        List list = new ArrayList();
        List<TIndexSysTypeCol> allByIndex_sys_id = this.targetManageIndexSysTypeColDao.findAllByIndex_sys_id(id);
        for (TIndexSysTypeCol tIndexSysTypeCol : allByIndex_sys_id) {
            if (tIndexSysTypeCol.getPid() == null){
                JSONObject treeObject = new JSONObject();
                treeObject.put("id",tIndexSysTypeCol.getId());
                treeObject.put("text",tIndexSysTypeCol.getName());
                if (tIndexSysTypeCol.getPid() != null){
                    treeObject.put("pid",tIndexSysTypeCol.getPid());
                }else {
                    treeObject.put("pid","");
                }
                if (tIndexSysTypeCol.getUnit() != null){
                    treeObject.put("unit",tIndexSysTypeCol.getUnit());
                }
                treeObject.put("type", tIndexSysTypeCol.getType());
                if (tIndexSysTypeCol.getPid() == null){
                    if (getChildren(tIndexSysTypeCol.getId()).size() > 0){
                        treeObject.put("nodes",getChildren(tIndexSysTypeCol.getId()));
                    }
                }
                list.add(treeObject);
            }
        }

        return list;
    }

    /**
     *唯一查询目标体系下的指标
     * @param id
     * @return
     */
    public JSONObject findSysTypeColOne(String id){
        TIndexSysTypeCol one = this.targetManageIndexSysTypeColDao.findOne(id);
        TIndexSysType tIndexSysType = this.targetManageIndexSysTypeDao.findOne(one.getType_id());
        //获取牵头部门
        TDepartment department = this.departmentDao.findOne(one.getDep());
        //获取责任部门
        String data_provider_id = one.getData_provider_id();
        String[] split = data_provider_id.split(",");
        List<String> zrbms = new ArrayList<>();
        for (int i = 0; i<split.length; i++){
            if (split[i] != null){
                TDepartment dep = this.departmentDao.findOne(split[i]);
                zrbms.add(dep.getDeptname());
            }
        }
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("indexCol",one);
        jsonObject.put("indexType",tIndexSysType);
        jsonObject.put("dep",department.getDeptname());
        jsonObject.put("data_provider_id",zrbms);
        return jsonObject;
    }

    /**
     * 查询指标体系下所有指标（非树形）
     * @param sysCode
     * @return
     */
    public List<TIndexSysTypeCol> findAllBySysCode(String sysCode){
        List<TIndexSysTypeCol> allByIndex_sys_id = this.targetManageIndexSysTypeColDao.findAllByIndex_sys_id(sysCode);
        return allByIndex_sys_id;
    }


    /**
     * 查询指标体系可选择的年份
     * @return
     */
    public JSONObject findYears(){
        JSONObject jsonObject = new JSONObject();
        BaseDaoImpl<TIndexSys,String> dao = new BaseDaoImpl<>(TIndexSys.class,entityManager);
        String sql = "select distinct t.year_ from T_INDEX_SYS t order by t.year_ asc";
        List query = dao.query(sql);
        jsonObject.put("years",query);
        return jsonObject;
    }


    public static File file;

    public static File getFile() {
        return file;
    }

    public static void setFile(File file) {
        TargetManageService.file = file;
    }

    /**
     * 下载excel模板
     * @param datas
     * @return 下载路径
     */
    public File downLoadExcel(IndexInfoVO datas) {
        //表头
        String[] heads = {"INDEX_CODE_", "INDEX_NAME_", "DIQU_", "DIQU_NAME_", "JH_WCL_", "JH_ZS_", "SJ_WCL_", "SJ_ZS_", "TIME_", "SCORE_"};
        //表头说明
        String[] headInfo = {"指标编码", "指标名称", "区域编码", "区域名称", "计划完成量", "计划增速", "实际完成量", "实际增速", "报告期", "打分"};


        HSSFWorkbook wb = null;
        FileOutputStream os = null;
        try {
            //创建excel
            wb = new HSSFWorkbook();
            //创建工作簿
            HSSFSheet sheet = wb.createSheet();
            //创建第一行表头
            HSSFRow headRow = sheet.createRow(0);
            for (int i = 0; i < heads.length; i++) {
                headRow.createCell(i).setCellValue(heads[i]);
            }
            //创建第二行，表头信息说明
            HSSFRow headInfoRow = sheet.createRow(1);
            for (int i = 0; i < headInfo.length; i++) {
                headInfoRow.createCell(i).setCellValue(headInfo[i]);
            }

            //读取部门信息
            List<TDepartment> departments = this.departmentDao.findAll();


            //写入指标信息
            Set<IndexVO> codes = datas.getCodes();
            int i = 2;
            for (IndexVO indexVO : codes) {
                for (int j = 0; j < departments.size(); j++){
                    HSSFRow dataRow = sheet.createRow(i);
                    dataRow.createCell(0).setCellValue(indexVO.getIndexCode());
                    dataRow.createCell(1).setCellValue(indexVO.getIndexName());
                    dataRow.createCell(8).setCellValue(datas.getTime());
                    TDepartment tDepartment = departments.get(j);
                    dataRow.createCell(3).setCellValue(tDepartment.getDeptname());
                    dataRow.createCell(2).setCellValue(tDepartment.getDeptnumber());
                    i++;
                }
            }

            TIndexSys byCode = this.targetManageIndexSysDao.findByCode(datas.getSysCode());
            String name = byCode.getName();
            String path = targetDownloadExcel;
            String fileName = name + datas.getTime() +  "目标体系模板.xls";

            File outFile = new File(path + File.separator + fileName);
            if (!outFile.getParentFile().exists()){
                outFile.getParentFile().mkdir();
            }

            os = new FileOutputStream(path + File.separator + fileName);
            //path = URLEncoder.encode(path,"UTF-8");
            //fileName = URLEncoder.encode(fileName,"UTF-8");

            wb.write(os);
            //os.flush();
            os.close();
            File file = new File(path + File.separator + fileName);
            setFile(file);
            return file;
        } catch (Exception e) {
            e.printStackTrace();
        }

        return null;
    }

    /**
     * 上传excel数据
     * @param multipartFile 文件
     * @param yearAndMonth 报告期
     * @param code 目标体系编码
     * @param flag 是否重新导入 yes-重新导入 其他-在后追加
     * @return
     */
    public Map<String,Object> uploadExcelToDatabase(MultipartFile multipartFile, String yearAndMonth, String code, String flag){
        Map<String, Object> result = new HashMap<>();
        //获取excel数据
        ExcelUtils.setFILED(0);
        List<Map<String, Object>> list = ExcelUtils.uploadExcel(multipartFile);
        if ( list == null){
            result.put("result","暂无数据");
            return result;
        }
        BaseDaoImpl<TIndexSysTypeColData,String> dao = new BaseDaoImpl<>(TIndexSysTypeColData.class,entityManager);
        String year = yearAndMonth.substring(0, 4);
        String tablename = "T_ECO_" + code + "_" + year;
        String deleteSql = "delete from  " + tablename + "  t  where t.time_ = '"+yearAndMonth+"'" ;
        if (flag.equals("yes")){
            int delete = dao.executeBySql(deleteSql);
            if (delete != -1){
                System.out.println("删除报告期数据");
            }
        }

        //循环行
        int success = 0;
        int filed = 0;
        for (int i = 0; i <  list.size(); i++){
            Map<String, Object> map = list.get(i);
            String sql = TableBuilderUtils.insetIntoTargetIndexSysTable(tablename, map);
            int a = dao.executeBySql(sql);
            if (a  != -1) {
                System.out.println("执行成功");
                success++;
            }
            else{
                System.out.println("执行sql失败" + sql);
                filed++;
            }
        }
        result.put("result","成功上传" + success + "条," + "失败" + ExcelUtils.getFILED() + "条");
        return result;
    }


}
